package com.sit.estatemanagementserver.dao;

import com.sit.estatemanagementserver.enity.Room;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class RoomDao {
    @Autowired
    DatabaseManager db;

    // 重构: 适配新的Room构造函数，并将数据库的REAL/DOUBLE类型转换为BigDecimal
    private Room resultSetToRoom(ResultSet rs) throws SQLException {
        return new Room(
                rs.getString(DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER),
                rs.getString(DatabaseContract.RoomEntry.COLUMN_OWNER_PHONE_FK),
                BigDecimal.valueOf(rs.getDouble(DatabaseContract.RoomEntry.COLUMN_WATER_BALANCE)),
                BigDecimal.valueOf(rs.getDouble(DatabaseContract.RoomEntry.COLUMN_ELECTRICITY_BALANCE)),
                BigDecimal.valueOf(rs.getDouble(DatabaseContract.RoomEntry.COLUMN_PROPERTY_FEE_BALANCE))
        );
    }

    public boolean addRoom(Room room) {
        String sql = "INSERT INTO " + DatabaseContract.RoomEntry.TABLE_NAME + " (" +
                DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + ", " +
                DatabaseContract.RoomEntry.COLUMN_WATER_BALANCE + ", " +
                DatabaseContract.RoomEntry.COLUMN_ELECTRICITY_BALANCE + ", " +
                DatabaseContract.RoomEntry.COLUMN_PROPERTY_FEE_BALANCE + ", " +
                DatabaseContract.RoomEntry.COLUMN_OWNER_PHONE_FK + ") VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, room.getRoomNumber());
            // 重构: 将BigDecimal转换为double存入数据库
            pstmt.setDouble(2, room.getWaterBalance().doubleValue());
            pstmt.setDouble(3, room.getElectricityBalance().doubleValue());
            pstmt.setDouble(4, room.getPropertyFeeBalance().doubleValue());
            pstmt.setString(5, room.getOwnerPhone());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error adding room '" + room.getRoomNumber() + "': " + e.getMessage());
            return false;
        }
    }

    public Room getRoom(String roomNumber) {
        String sql = "SELECT * FROM " + DatabaseContract.RoomEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, roomNumber);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToRoom(rs);
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting room '" + roomNumber + "': " + e.getMessage());
        }
        return null;
    }

    public List<Room> getAllRooms() {
        List<Room> rooms = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.RoomEntry.TABLE_NAME;
        try (Connection conn = db.connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                rooms.add(resultSetToRoom(rs));
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting all rooms: " + e.getMessage());
        }
        return rooms;
    }

    public List<Room> getRoomsByOwner(String ownerPhone) {
        List<Room> rooms = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.RoomEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.RoomEntry.COLUMN_OWNER_PHONE_FK + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, ownerPhone);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    rooms.add(resultSetToRoom(rs));
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting rooms by owner '" + ownerPhone + "': " + e.getMessage());
        }
        return rooms;
    }

    public boolean updateRoom(Room room) {
        String sql = "UPDATE " + DatabaseContract.RoomEntry.TABLE_NAME + " SET " +
                DatabaseContract.RoomEntry.COLUMN_WATER_BALANCE + " = ?, " +
                DatabaseContract.RoomEntry.COLUMN_ELECTRICITY_BALANCE + " = ?, " +
                DatabaseContract.RoomEntry.COLUMN_PROPERTY_FEE_BALANCE + " = ?, " +
                DatabaseContract.RoomEntry.COLUMN_OWNER_PHONE_FK + " = ? WHERE " +
                DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 重构: 将BigDecimal转换为double存入数据库
            pstmt.setDouble(1, room.getWaterBalance().doubleValue());
            pstmt.setDouble(2, room.getElectricityBalance().doubleValue());
            pstmt.setDouble(3, room.getPropertyFeeBalance().doubleValue());
            pstmt.setString(4, room.getOwnerPhone());
            pstmt.setString(5, room.getRoomNumber());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error updating room '" + room.getRoomNumber() + "': " + e.getMessage());
            return false;
        }
    }

    public boolean deleteRoom(String roomNumber) {
        String sql = "DELETE FROM " + DatabaseContract.RoomEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, roomNumber);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error deleting room '" + roomNumber + "': " + e.getMessage());
            return false;
        }
    }
}